<?php
	ob_start();
	include("../header.php"); //header \
	include("../lib/header.php");
	include("../lib/function.php");
?>
<script type="text/javascript">
	function show_popup(page)
	{
		child = open
		(
			page,
			"",
			"scrollbars,width=400,height=400,left=150,top=150"
		);
	}
</script>
<?
	$show		= $HTTP_GET_VARS['show'];
	$page 		= $HTTP_GET_VARS['page']; // get nomor halaman yang dipanggil
	$next		= $HTTP_GET_VARS['next']; // get segmen halaman yang dipanggil
	$pendidikan = $HTTP_GET_VARS['pendidikan'];
	$pengalaman	= $HTTP_GET_VARS['pengalaman'];
	$nama		= $HTTP_GET_VARS['nama'];
	$alamat		= $HTTP_GET_VARS['alamat'];
	$skill		= $HTTP_GET_VARS['skill'];
	$dari		= $HTTP_GET_VARS['dari'];
	$sampai		= $HTTP_GET_VARS['sampai'];
	$tgl_awal	= $HTTP_GET_VARS['tgl_awal'];
	$tgl_akhir	= $HTTP_GET_VARS['tgl_akhir'];
	
?>
<br />
<fieldset>
 <legend>Pencarian Detail Pelamar</legend>
 <form action="<?=$PHP_SELF?>" method="get" name="f1">
 <table align="center" width="97%" border="0" cellpadding="2" cellspacing="1">
  <tr>
   <td colspan="5">Pencarian Berdasarkan:</td>
  </tr>
  <tr>
   <td>Nama Pelamar:</td>
   <td><input type="text" name="nama" size="35" /></td>
   <td>&nbsp;</td>
   <td>Usia</td>
   <td><input type="text" name="dari" size="5" maxlength="5" /> S/D <input type="text" name="sampai" maxlength="5" size="5" /> (tahun)</td>
  </tr>
  <tr>
   <td>Alamat:</td>
   <td><input type="text" size="35" name="alamat" /></td>
   <td>&nbsp;</td>
   <td>Pendidikan</td>
   <td>
      <select name="jenjang" id="JenjangPendidikan">
      <option value="">-Select-</option>
      <?
        $sP	= mssql_query("SELECT * FROM Jenjang ORDER BY ordernum",$conn);
        while($rP = mssql_fetch_array($sP)){
            echo"<option value='$rP[id]'>$rP[nama]</option>";
        }
      ?>
      </select>
	  &nbsp;
      <select name="fakultas" id="FakultasPendidikan">
        <option value="">-Select-</option>
      </select>
      &nbsp;
      <select name="jurusan" id="JurusanPendidikan">
      <option value="">-Select-</option>
      </select>
   
	   <!--select name='pendidikan'>
    	<option value=''>--- Silahkan Pilih ---</option>
		<?
		$sqlP = mssql_query("SELECT * FROM MstPendidikan ORDER BY keterangan,jurusan ASC",$conn);
		while($rowP = mssql_fetch_array($sqlP)){
			//echo"<option value='$rowP[Pend_ID]'>$rowP[keterangan] - $rowP[jurusan]</option>";
		}
		?>
	   </select-->
   </td>
  </tr>
  <tr>
   <td>Pengalaman Kerja</td>
   <td><input type="text" name="pengalaman" size="35" /></td>
   <td>&nbsp;</td>
   <td>Skill</td>
   <td><input type="text" name="skill" size="35" /></td>
  </tr>
  <tr>
   <td>Periode Tanggal Lamaran</td>
   <td colspan="4">
    <input type="text" name="tgl_awal" size="11" maxlength="10" />
    <a href="javascript:cal1.popup();"><img src="<?=$path?>/images/cal.gif" width="16" height="16" border="0" alt="Kalendar" title="Kalendar"></a> 
    s.d
    <input type="text" name="tgl_akhir" size="11" maxlength="10" />
    <a href="javascript:cal2.popup();"><img src="<?=$path?>/images/cal.gif" width="16" height="16" border="0" alt="Kalendar" title="Kalendar"></a> 
   </td>
  </tr>
  <tr>
   <td colspan="3">&nbsp;</td>
   <td colspan="2">
   <input type="submit" value="Cari" class="button" />
   <input type="reset" value="Reset" class="button" />
   <input type=button value="Tambah Data" class=button Onclick="Javascript:location.href='../form/frm_emp.php'">
   <input type="button" onclick="Javascript:location.href='exp_excel_pel.php?nama=<?=$nama?>&amp;alamat=<?=$alamat?>&amp;pengalaman=<?=$pengalaman?>&amp;skill=<?=$skill?>&amp;pendidikan=<?=$pendidikan?>&amp;dari=<?=$dari?>&amp;sampai=<?=$sampai?>&amp;tgl_awal=<?=$tgl_awal?>&amp;tgl_akhir=<?=$tgl_akhir?>'" value="Kirim ke Excel" class="button" /></td>
   
  </tr>
 </table>
 </form>
</fieldset><br />
<?

	$sql  = "WITH ASD AS (select *, year(getdate()) - year(Tgl_Lahir) Usia from karyawan A where pelamar='1' ";
	
	if(!empty($HTTP_GET_VARS['pendidikan'])){
		$sql .= " AND A.NIK in (SELECT NIK FROM Pendidikan WHERE Pend_ID='".$HTTP_GET_VARS['pendidikan']."' AND NIK=A.NIK)";
	}
	
	if(!empty($HTTP_GET_VARS['pengalaman'])){
		$sql .= " AND A.NIK in (SELECT NIK FROM Karir WHERE (jabatan LIKE '%".strtoupper($HTTP_GET_VARS['pengalaman'])."%' OR instansi LIKE '%".$HTTP_GET_VARS['pengalaman']."%') AND NIK=A.NIK) ";
	}
	
	if(!empty($HTTP_GET_VARS['skill'])){
		$sql .= " AND A.NIK in (SELECT NIK FROM Kursus WHERE Program LIKE '%".strtoupper($HTTP_GET_VARS['skill'])."%' AND NIK=A.NIK)";
	}
	
	$sql .= " ) SELECT count(*) FROM ASD";
	
	$sql .= " WHERE Nama LIKE '%".strtoupper($HTTP_GET_VARS['nama'])."%' AND Alamat LIKE '%".strtoupper($HTTP_GET_VARS['alamat'])."%'";

	if(!empty($HTTP_GET_VARS['dari']) || !empty($HTTP_GET_VARS['sampai'])){
		$sql .= " AND ((year(getdate()) - year(Tgl_Lahir)) BETWEEN '".$HTTP_GET_VARS['dari']."' AND '".$HTTP_GET_VARS['sampai']."') ";
	}
	
	// pencarian berdasarkan periode tanggal
	// 01/12/2007
	// 01234567890
	// 
	$t1 = $_GET['tgl_awal'];
	$t2 = $_GET['tgl_akhir'];
	if(!empty($t1) && !empty($t2)){
		// konversi tanggal awal dmy to mdy
		$d = substr($t1,0,2);
		$m = substr($t1,3,2);
		$y = substr($t1,6,4);
		
		$taw = $m."/".$d."/".$y; 
		
		$d1 = substr($t2,0,2);
		$m1 = substr($t2,3,2);
		$y1 = substr($t2,6,4);
		
		$tak = $m1."/".$d1."/".$y1; 
		
		$sql .= " AND (datecreated between '".$taw."' and '".$tak."') ";
	}

	//echo $sql;
	$query = mssql_query($sql,$conn);

	$total	= mssql_fetch_row($query);
	$limit	= 10;
	$pager  = Pager::getPagerData($total[0], $limit, $page);
	$offset = $pager->offset;
	$limit  = $pager->limit;
	$page   = $pager->page;

	if($total > 0){
?>

<?
	PagingPel($pager,$page,$next,$limit,$nama,$alamat,$pengalaman,$dari,$sampai,$pendidikan,$skill,$tgl_awal,$tgl_akhir);
	//echo"$sql";
?>
<table width="100%">
 <tr class="headlist">
  <td>No</td>
  <td>NIK</td>
  <td>Nama</td>
  <td>Alamat</td>
  <td>Usia</td>
  <td>Pendidikan</td>
  <td>Pengalaman</td>
  <td>Kursus</td>
  <td>Tgl Lamaran</td>
 </tr>
 	<?
	$sqlccc  = "WITH ASD1 AS (select ROW_NUMBER() OVER(ORDER BY pelamar) AS No, A.NIK,Nama,Alamat, (year(getdate()) - year(Tgl_Lahir)) as Usia, pelamar, convert(char(12), DateCreated, 103) tgl_lamaran, datecreated from karyawan A where pelamar='1' ";
	
	if(!empty($HTTP_GET_VARS['pendidikan'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Pendidikan WHERE Pend_ID='".strtoupper($HTTP_GET_VARS['pendidikan'])."' AND NIK=A.NIK)";
	}
	
	if(!empty($HTTP_GET_VARS['pengalaman'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Karir WHERE (jabatan LIKE '%".strtoupper($HTTP_GET_VARS['pengalaman'])."%' OR instansi LIKE '%".$HTTP_GET_VARS['pengalaman']."%') AND NIK=A.NIK) ";
	}
	
	if(!empty($HTTP_GET_VARS['skill'])){
		$sqlccc .= " AND A.NIK in (SELECT NIK FROM Kursus WHERE Program LIKE '%".strtoupper($HTTP_GET_VARS['skill'])."%' AND NIK=A.NIK)";
	}

	$sqlccc .= " AND Nama LIKE '%".strtoupper($HTTP_GET_VARS['nama'])."%' AND Alamat LIKE '%".strtoupper($HTTP_GET_VARS['alamat'])."%'";

	if(!empty($HTTP_GET_VARS['dari']) || !empty($HTTP_GET_VARS['sampai'])){
		$sqlccc .= " AND ((year(getdate()) - year(Tgl_Lahir)) BETWEEN '".$HTTP_GET_VARS['dari']."' AND '".$HTTP_GET_VARS['sampai']."') ";
	}

	// pencarian berdasarkan periode tanggal
	// 01/12/2007
	// 01234567890
	// 
	$t1 = $_GET['tgl_awal'];
	$t2 = $_GET['tgl_akhir'];
	if(!empty($t1) && !empty($t2)){
		// konversi tanggal awal dmy to mdy
		$d = substr($t1,0,2);
		$m = substr($t1,3,2);
		$y = substr($t1,6,4);
		
		$taw = $m."/".$d."/".$y; 
		
		$d1 = substr($t2,0,2);
		$m1 = substr($t2,3,2);
		$y1 = substr($t2,6,4);
		
		$tak = $m1."/".$d1."/".$y1; 
		
		$sqlccc .= " AND (datecreated between '".$taw."' and '".$tak."') ";
	}

	$sqlccc.= " ) SELECT * FROM ASD1 WHERE No BETWEEN $offset AND ".($limit+$offset)."";
	
	//echo $sqlccc;
		$sqlcccd = mssql_query($sqlccc,$conn);
    	while($row = mssql_fetch_array($sqlcccd)){
			echo"
			 <tr class=contentlist>
			  <td align=right>$row[No]</td>
			  <td>$row[NIK]</td>
			  <td>$row[Nama]</td>
			  <td>$row[Alamat]</td>
			  <td>$row[Usia]</td>";
			// get pendidikan
			$sqlpend = mssql_query("SELECT jen.nama AS jennama, fak.nama AS faknama, jur.nama AS jurnama 
									FROM Pendidikan pen
									LEFT JOIN Jenjang jen ON jen.id = pen.jenjang_id 
									LEFT JOIN Fakultas fak ON fak.id = pen.fakultas_id
									LEFT JOIN Jurusan jur ON jur.id = pen.jurusan_id
									WHERE pen.NIK='$row[NIK]'
								   ",$conn);
			$rowpend = mssql_fetch_array($sqlpend);
			  echo"<td>$rowpend[jennama] $rowpend[faknama] $rowpend[jurnama]</td>";
			  echo"<td>";
			  // get pengalaman
			  $skarir = mssql_query("SELECT * FROM Karir WHERE NIK='$row[NIK]'");
			  while($rkarir = mssql_fetch_array($skarir)){
			  	echo"&bull; $rkarir[jabatan], $rkarir[instansi] <br>";
			  }
			echo"</td><td>";
			  // get kursus
			  $skursus = mssql_query("SELECT * FROM Kursus WHERE NIK='$row[NIK]'");
			  while($rkursus = mssql_fetch_array($skursus)){
			  	echo"&bull; $rkursus[Program] <br>";
			  }
			echo"</td>  
			<td>$row[tgl_lamaran]</td>
			 </tr>
			";
		}
	?>
</table>
<?
	}
echo"</div>
	  </td>
	   </tr>
	    </table>
";
include("../menu.php");
?>
<script type="text/javascript">
// script untuk popup calendar
	var cal1 = new calendar1(document.forms['f1'].elements['tgl_awal']);
	cal1.year_scroll = true;
	cal1.time_comp = false;

	var cal2 = new calendar1(document.forms['f1'].elements['tgl_akhir']);
	cal2.year_scroll = true;
	cal2.time_comp = false;
	
</script>